import pymssql
import pandas as pd


class MSSql:
    def __init__(self, host, port, user, password, database):
        self.conn = pymssql.connect(host=host, port=port, user=user, password=password, database=database, charset="CP936")

    def get_tables(self):
        """
        查询所有表名
        :return:
        """
        cursor = self.conn.cursor()
        cursor.execute(f"select name from sysobjects where xtype='u'")
        return cursor.fetchall()

    def get_columns_info(self, table_name):
        """

        :return:
        """
        sql = f"select name from syscolumns where id=(select max(id) from sysobjects where xtype='u' and name='{table_name}')"
        cursor = self.conn.cursor(sql)
        return cursor.fetchall()

    def get_table_data(self, table_name):
        """
        查询表信息
        :param table_name:
        :return:
        """
        sql_command = f"SELECT * FROM {table_name}"
        df_chunk = pd.read_sql(sql_command, self.conn, chunksize=1000)
        df_data = pd.concat(df_chunk)
        df_data.to_csv(f'mssql_{table_name}.csv', sep=',', header=True, index=False)
        return df_data


